﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Sida.Common;


namespace AccessControl.Sida.DataAccess
{
	public class StudentBasicInfoAccess
	{

        public DataTable SelectStudentByFilter(List<string> filters)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var whereClause = "";
                if (filters != null && filters.Count>0)
                    whereClause += " AND " + string.Join(" AND ", filters);

                var cmdSelect =
                    new SqlCommand(String.Format(
                            "SELECT fresh.nameresh ,fsf.stcode,fsf.name,fsf.family,fsf.namep,fsf.sal_vorod,fsf.idd_meli,fsf2.date_tav FROM fsf INNER JOIN fsf2 ON fsf.stcode = fsf2.stcode INNER JOIN fresh ON fsf.idresh=fresh.id WHERE ((fsf.idvazkol=1) OR (fsf.idvazkol=2)) {0} ORDER BY fsf.stcode DESC", whereClause), cn);

                cn.Open();
                var studentBasicInfoTable = new SidaDS.StudentBasicInfoDataTable();
                var reader = cmdSelect.ExecuteReader();
                if (reader != null)
                    while (reader.Read())
                    {
                        var newRow = studentBasicInfoTable.NewStudentBasicInfoRow();
                        newRow.STCode = (String)reader["stcode"];
                        newRow.Nameresh = (String)reader["nameresh"];
                        newRow.Name = (String)reader["name"];
                        newRow.Family = (String)reader["family"];
                        newRow.Sal_Vorod = (string)reader["sal_vorod"];
                        newRow.Namep = (string)reader["namep"];
                        if (reader["idd_meli"] != DBNull.Value)
                            newRow.Idd_meli = (string)reader["idd_meli"];
                        if (reader["date_tav"] != DBNull.Value)
                            newRow.Date_tav = (string)reader["date_tav"];
                        studentBasicInfoTable.AddStudentBasicInfoRow(newRow);
                    }
                cn.Close();
                return studentBasicInfoTable;
            }
        }

		public SidaDS.StudentBasicInfoDataTable SelectStudent(String studentCode, String name, String family, String idd_meli)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var whereClause = "";
				if (studentCode != String.Empty)
					whereClause += " AND fsf.stcode LIKE @stcode +'%'";
				if (name != String.Empty)
					whereClause += " AND fsf.name LIKE @name +'%'";
				if (family != String.Empty)
					whereClause += " AND fsf.family LIKE @family +'%'";
				if (idd_meli != String.Empty)
					whereClause += " AND fsf.idd_meli LIKE @idd_meli +'%'";


				var cmdSelect =
					new SqlCommand(String.Format(
							"SELECT fresh.nameresh ,fsf.stcode,fsf.name,fsf.family,fsf.namep,fsf.sal_vorod,fsf.idd_meli,fsf2.date_tav FROM fsf INNER JOIN fsf2 ON fsf.stcode = fsf2.stcode INNER JOIN fresh ON fsf.idresh=fresh.id WHERE ((fsf.idvazkol=1) OR (fsf.idvazkol=2)) {0} ORDER BY fsf.stcode DESC", whereClause), cn);
				if (studentCode != String.Empty)
					cmdSelect.Parameters.AddWithValue("@stcode", studentCode);
				if (name != String.Empty)
					cmdSelect.Parameters.AddWithValue("@name", name);
				if (family != String.Empty)
					cmdSelect.Parameters.AddWithValue("@family", family);
				if (idd_meli != String.Empty)
					cmdSelect.Parameters.AddWithValue("@idd_meli", idd_meli);
				var studentBasicInfoTable = new SidaDS.StudentBasicInfoDataTable();
				cn.Open();
				var reader = cmdSelect.ExecuteReader();
				if (reader != null)
					while (reader.Read())
					{
						var newRow = studentBasicInfoTable.NewStudentBasicInfoRow();
						newRow.STCode = (String)reader["stcode"];
						newRow.Nameresh = (String)reader["nameresh"];
						newRow.Name = (String)reader["name"];
						newRow.Family = (String)reader["family"];
						newRow.Sal_Vorod = (string)reader["sal_vorod"];
						newRow.Namep = (string)reader["namep"];
						if (reader["idd_meli"] != DBNull.Value)
							newRow.Idd_meli = (string)reader["idd_meli"];
						if (reader["date_tav"] != DBNull.Value)
							newRow.Date_tav = (string)reader["date_tav"];
						studentBasicInfoTable.AddStudentBasicInfoRow(newRow);
					}
				cn.Close();
				return studentBasicInfoTable.Rows.Count != 0 ? studentBasicInfoTable : null;
			}
		}

		public Decimal SelectStudentRemainAccount(String studentCode)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				try
				{
					var cmdSelect =
						new SqlCommand("SpRemainAccount", cn) {CommandType = CommandType.StoredProcedure};
					cmdSelect.Parameters.AddWithValue("@stcode", studentCode);
					cn.Open();
					var dataReader = cmdSelect.ExecuteReader();
					Decimal value = 0;
					if (dataReader.Read())
						value = (Decimal) dataReader["Remain"];
					cn.Close();
					return value;
				}
				catch (Exception ex)
				{
					throw ex;
				}
			}
		}

		public SidaDS.NotMojaz_SabtenamDataTable SelectNotMojazSabtenam (String studentCode)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdSelect =
					new SqlCommand("SELECT sharh,nameuser FROM notmojaz_sabtenam WHERE stcode=@stcode", cn);
					cmdSelect.Parameters.AddWithValue("@stcode", studentCode);
					var notMojazSabtenamDataTable = new SidaDS.NotMojaz_SabtenamDataTable();
				cn.Open();
				var reader = cmdSelect.ExecuteReader();
				if (reader != null)
					while (reader.Read())
					{
						var newRow = notMojazSabtenamDataTable.NewNotMojaz_SabtenamRow();
						newRow.Sharh = (String)reader["sharh"];
						newRow.Nameuser = (String)reader["nameuser"];
						notMojazSabtenamDataTable.AddNotMojaz_SabtenamRow(newRow);
					}
				cn.Close();
				return notMojazSabtenamDataTable.Rows.Count != 0 ? notMojazSabtenamDataTable : null;
			}
		}

        public static SidaDS.StudentBasicInfoRow GetStudentStatus(string studentNumber)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                SidaDS.StudentBasicInfoRow newRow = null;
                var cmdSelect = new SqlCommand(@"SELECT fsf.idvazkol,fresh.nameresh ,fsf.stcode,fsf.name,fsf.family,fsf.namep,fsf.sal_vorod,fsf.idd_meli,fsf2.date_tav 
                                                FROM fsf INNER JOIN fsf2 ON fsf.stcode = fsf2.stcode 
                                                INNER JOIN fresh ON fsf.idresh=fresh.id 
                                            WHERE fsf.stcode=@stcode", cn);
                cmdSelect.Parameters.Add("@stcode", SqlDbType.VarChar, 100).Value = studentNumber;
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        newRow = new SidaDS.StudentBasicInfoDataTable().NewStudentBasicInfoRow();
                        try{
                            newRow.idvazkol = Convert.ToInt32(reader["idvazkol"]);
                            newRow.STCode = (String)reader["stcode"];
                            newRow.Nameresh = (String)reader["nameresh"];
                            newRow.Name = (String)reader["name"];
                            newRow.Family = (String)reader["family"];
                            newRow.Sal_Vorod = (string)reader["sal_vorod"];
                            newRow.Namep = (string)reader["namep"];
                            if (reader["idd_meli"] != DBNull.Value)
                                newRow.Idd_meli = (string)reader["idd_meli"];
                            if (reader["date_tav"] != DBNull.Value)
                                newRow.Date_tav = (string)reader["date_tav"];
                            
                        }
                        catch (Exception){
                            
                        }
                    }
                }
                finally
                {
                    cn.Close();
                }
                return newRow;
            }
        }

	}
}
